package edu.cdbi.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.cdbi.model.instance.Teacher;

public class TeacherManagement {

	public TeacherManagement() {

	}

	public Integer insert(String name, String email) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return null;
		}

		try {
			PreparedStatement statement = connection
					.prepareStatement("INSERT INTO teacher (name, email) VALUES(?, ?)");
			statement.setString(1, name);
			statement.setString(2, email);
			statement.executeUpdate();
			statement.close();

			statement = connection.prepareStatement("SELECT @@identity");
			ResultSet resultSet = statement.executeQuery();
			resultSet.next();
			int number = resultSet.getInt(1);
			statement.close();
			connection.close();
			return number;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<Teacher> getList() {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return null;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("SELECT teacher_id, name, email FROM teacher");
			ResultSet resultSet = statement.executeQuery();
			List<Teacher> list = new ArrayList<Teacher>();
			while (resultSet.next()) {
				Teacher teacher = new Teacher();
				teacher.setId(resultSet.getInt("teacher_id"));
				teacher.setName(resultSet.getString("name"));
				teacher.setEmail(resultSet.getString("email"));
				list.add(teacher);
			}
			resultSet.close();
			statement.close();
			connection.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public Teacher getData(int id) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return null;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("SELECT teacher_id, name, email FROM teacher "+
							"WHERE teacher_id=?");
			statement.setInt(1, id);
			ResultSet resultSet = statement.executeQuery();
			Teacher teacher = null;
			if (resultSet.next()) {
				teacher = new Teacher();
				teacher.setId(resultSet.getInt("teacher_id"));
				teacher.setName(resultSet.getString("name"));
				teacher.setEmail(resultSet.getString("email"));
			}
			resultSet.close();
			statement.close();
			connection.close();
			return teacher;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean delete(int id) {
		Connection connection = DatabaseManager.getInstance()
				.createConnection();
		if (connection == null) {
			return false;
		}
		try {
			PreparedStatement statement = connection
					.prepareStatement("DELETE FROM teacher WHERE teacher_id=" + id);
			statement.executeUpdate();
			statement.close();
			connection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
}
